package com.webmarmun;

import java.awt.print.PrinterException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.RowSorter;
import javax.swing.SortOrder;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;

public class Reports extends javax.swing.JFrame {

    public String serverName = "localhost";
    public String mydatabase = "porotnici";
    public String url = "jdbc:mysql://" + serverName + "/" + mydatabase + "?useUnicode=true&characterEncoding=utf8";
    public String username = "root";
    public String password = "";
    String mesecString;
    String godinaString;

    public Reports() {

        initComponents();


        try {

            java.sql.Connection connection = null;
            connection = DriverManager.getConnection(url, username, password);
            java.sql.PreparedStatement t = connection.prepareStatement("SELECT * from porotnik ");

            ResultSet trs = null;
            trs = t.executeQuery();


            while (trs.next()) {
                jComboBox1.addItem(trs.getString("ime"));


            }





        } catch (SQLException ex) {
            Logger.getLogger(Izmena.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel1 = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        jComboBox1 = new javax.swing.JComboBox();
        jButton1 = new javax.swing.JButton();
        jMonthChooser1 = new com.toedter.calendar.JMonthChooser();
        jLabel2 = new javax.swing.JLabel();
        jYearChooser1 = new com.toedter.calendar.JYearChooser();
        jLabel3 = new javax.swing.JLabel();
        jPanel2 = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jButton2 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);

        jPanel1.setLayout(new org.netbeans.lib.awtextra.AbsoluteLayout());

        jLabel1.setText("Изаберите годину");
        jPanel1.add(jLabel1, new org.netbeans.lib.awtextra.AbsoluteConstraints(130, 10, -1, -1));

        jComboBox1.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Изаберите поротника" }));
        jComboBox1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jComboBox1ActionPerformed(evt);
            }
        });
        jPanel1.add(jComboBox1, new org.netbeans.lib.awtextra.AbsoluteConstraints(270, 40, 196, 28));

        jButton1.setText("Креирај извештај");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });
        jPanel1.add(jButton1, new org.netbeans.lib.awtextra.AbsoluteConstraints(584, 44, 147, 27));
        jPanel1.add(jMonthChooser1, new org.netbeans.lib.awtextra.AbsoluteConstraints(10, 40, -1, 29));

        jLabel2.setText(" (ако ово поље остане празно креираће се групни месечни извештај)");
        jPanel1.add(jLabel2, new org.netbeans.lib.awtextra.AbsoluteConstraints(270, 10, -1, -1));
        jPanel1.add(jYearChooser1, new org.netbeans.lib.awtextra.AbsoluteConstraints(130, 40, 60, 30));

        jLabel3.setText("Изаберите месец");
        jPanel1.add(jLabel3, new org.netbeans.lib.awtextra.AbsoluteConstraints(10, 10, -1, -1));

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null}
            },
            new String [] {
                "Датум", "Име поротника", "Број сати", "Цена по сату", "Превоз", "Паушално", "Укупно"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.String.class, java.lang.String.class, java.lang.Integer.class, java.lang.Float.class, java.lang.Float.class, java.lang.Float.class, java.lang.Float.class
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }
        });
        jTable1.setFillsViewportHeight(true);
        jTable1.setRowHeight(25);
        jScrollPane1.setViewportView(jTable1);
        jTable1.getColumnModel().getColumn(0).setMinWidth(80);
        jTable1.getColumnModel().getColumn(0).setPreferredWidth(80);
        jTable1.getColumnModel().getColumn(0).setMaxWidth(80);
        jTable1.getColumnModel().getColumn(1).setMinWidth(180);
        jTable1.getColumnModel().getColumn(1).setPreferredWidth(180);
        jTable1.getColumnModel().getColumn(1).setMaxWidth(180);

        jButton2.setText("Штампа");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                .addContainerGap(23, Short.MAX_VALUE)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 832, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton2))
                .addGap(20, 20, 20))
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                .addGap(18, 18, 18)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 396, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jButton2)
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, 899, Short.MAX_VALUE))
                    .addGroup(layout.createSequentialGroup()
                        .addGap(23, 23, 23)
                        .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, 93, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(26, 26, 26)
                .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(31, Short.MAX_VALUE))
        );

        java.awt.Dimension screenSize = java.awt.Toolkit.getDefaultToolkit().getScreenSize();
        setBounds((screenSize.width-935)/2, (screenSize.height-656)/2, 935, 656);
    }// </editor-fold>//GEN-END:initComponents

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        for (int r = 0; r < ((DefaultTableModel) jTable1.getModel()).getRowCount(); r++) {
                ((DefaultTableModel) jTable1.getModel()).removeRow(r);
        }
        for (int r = 0; r < ((DefaultTableModel) jTable1.getModel()).getRowCount(); r++) {
                ((DefaultTableModel) jTable1.getModel()).removeRow(r);
        }
        ((DefaultTableModel) jTable1.getModel()).setNumRows(1000);
        int mesec = jMonthChooser1.getMonth();
        int godina = jYearChooser1.getYear();
        godinaString = Integer.toString(godina);
        if (mesec == 0) {
            mesecString = "01";
        } else if (mesec == 1) {
            mesecString = "02";
        } else if (mesec == 2) {
            mesecString = "03";
        } else if (mesec == 3) {
            mesecString = "04";
        } else if (mesec == 4) {
            mesecString = "05";
        } else if (mesec == 5) {
            mesecString = "06";
        } else if (mesec == 6) {
            mesecString = "07";
        } else if (mesec == 7) {
            mesecString = "08";
        } else if (mesec == 8) {
            mesecString = "09";
        } else if (mesec == 9) {
            mesecString = "10";
        } else if (mesec == 10) {
            mesecString = "11";
        } else if (mesec == 11) {
            mesecString = "12";
        }
        String searchString = mesecString + "-" + godinaString;
        ArrayList imena = getList();
        imena = removeDuplicateWithOrder(imena);
        Collections.sort(imena);

        int g;
        int i;

        float Floattotal = 0;
        float TOTALNO = 0;
        int ukupanBrojSati=0;
        float POSATU = 0;
        float ukupanPrevoz=0;
        float ukupnoPausalno=0;
        int l = 0;
        String imePorotnika = null;
        if (jComboBox1.getSelectedIndex() == 0) {
            try {

                java.sql.Connection connection = null;
                connection = DriverManager.getConnection(url, username, password);
                // Iterator itr = imena.iterator();

                // while (itr.hasNext()) {
                for (Iterator itr = imena.iterator(); itr.hasNext();) {

                    int BrojSatiPorotnika = 0;
                    float PoSatu = 0;
                    float PrevozPorotnika = 0;
                    float PausalnoPorotnika = 0;
                    float totalno = 0;

                    java.sql.PreparedStatement t = connection.prepareStatement("SELECT * from  evidencija where ime_porotnika ='" + itr.next() + "'");

                    ResultSet trs = t.executeQuery();

                    while (trs.next()) {

                        String datumIzBaze = trs.getString("datum");
                        int index1 = datumIzBaze.indexOf(searchString);
                        if (index1 != -1) {

                            imePorotnika = trs.getString("ime_porotnika");
                            BrojSatiPorotnika += trs.getInt("broj_sati");
                            PoSatu = trs.getFloat("po_satu");
                            PrevozPorotnika += trs.getFloat("prevoz");
                            PausalnoPorotnika += trs.getFloat("pausalno");

                        }


                    }
                    totalno = (BrojSatiPorotnika * PoSatu) + PrevozPorotnika + PausalnoPorotnika;
                    TOTALNO += totalno;
                    ukupanBrojSati+=BrojSatiPorotnika;
                    POSATU=PoSatu;
                    ukupanPrevoz+=PrevozPorotnika;
                    ukupnoPausalno+=PausalnoPorotnika;
                    if (totalno != 0) {
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(mesecString + "-" + godinaString, l, 0);        //
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(imePorotnika, l, 1);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(BrojSatiPorotnika, l, 2);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(PoSatu, l, 3);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(PrevozPorotnika, l, 4);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(PausalnoPorotnika, l, 5);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(totalno, l, 6);
                    }

                    l++;


                }




            } catch (SQLException ex) {
                Logger.getLogger(Izmena.class.getName()).log(Level.SEVERE, null, ex);

            }
            ((DefaultTableModel) jTable1.getModel()).setRowCount(l);
             TableRowSorter<TableModel> sorter = new TableRowSorter<TableModel>(((DefaultTableModel) jTable1.getModel()));
        List<RowSorter.SortKey> sortKeys = new ArrayList<RowSorter.SortKey>();
        sortKeys.add(new RowSorter.SortKey(1, SortOrder.ASCENDING));

        sorter.setSortKeys(sortKeys);

        jTable1.setRowSorter(sorter);

        
        } else {
            try {
                java.sql.Connection connection = null;
                connection = DriverManager.getConnection(url, username, password);
                java.sql.PreparedStatement t = connection.prepareStatement("SELECT datum,ime_porotnika,broj_sati,po_satu,prevoz,pausalno,ukupno from evidencija where ime_porotnika='" + jComboBox1.getSelectedItem().toString() + "' ");
                ResultSet trs = null;
                trs = t.executeQuery();

                while (trs.next()) {
                    String datumIzBaze = trs.getString("datum");
                    int index1 = datumIzBaze.indexOf(searchString);
                    if (index1 != -1) {
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getString("datum"), l, 0);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getString("ime_porotnika"), l, 1);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getInt("broj_sati"), l, 2);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getFloat("po_satu"), l, 3);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getFloat("prevoz"), l, 4);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getFloat("pausalno"), l, 5);
                        ((DefaultTableModel) jTable1.getModel()).setValueAt(trs.getFloat("ukupno"), l, 6);
                        TOTALNO += trs.getFloat("ukupno");
                        ukupanBrojSati+=trs.getInt("broj_sati");
                        POSATU=trs.getFloat("po_satu");
                        ukupanPrevoz+=trs.getFloat("prevoz");
                        ukupnoPausalno+=trs.getFloat("pausalno");
                        l++;
                    }

                }

                 ((DefaultTableModel) jTable1.getModel()).setRowCount(l);
                

        

            } catch (SQLException ex) {
                Logger.getLogger(Izmena.class.getName()).log(Level.SEVERE, null, ex);

            }
            




        }

        for (int r = 0; r < ((DefaultTableModel) jTable1.getModel()).getRowCount(); r++) {

            if (((DefaultTableModel) jTable1.getModel()).getValueAt(r, 0) == null) {

                ((DefaultTableModel) jTable1.getModel()).removeRow(r);
            }

        }

        for (int r = 0; r < ((DefaultTableModel) jTable1.getModel()).getRowCount(); r++) {

            if (((DefaultTableModel) jTable1.getModel()).getValueAt(r, 0) == null) {

                ((DefaultTableModel) jTable1.getModel()).removeRow(r);
            }

        }
        jTable1.getColumnModel().getColumn(1).setPreferredWidth(180);
       ((DefaultTableModel) jTable1.getModel()).insertRow(((DefaultTableModel) jTable1.getModel()).getRowCount(), new Object[]{"УКУПНО","~",ukupanBrojSati,POSATU,ukupanPrevoz,ukupnoPausalno,TOTALNO});
        TableRowSorter<TableModel> sorter = new TableRowSorter<TableModel>(((DefaultTableModel) jTable1.getModel()));
        List<RowSorter.SortKey> sortKeys = new ArrayList<RowSorter.SortKey>();
        sortKeys.add(new RowSorter.SortKey(0, SortOrder.ASCENDING));
        sortKeys.add(new RowSorter.SortKey(1, SortOrder.ASCENDING));
      
        sorter.setSortKeys(sortKeys);
        jTable1.setRowSorter(sorter);
        jTable1.getRowSorter().convertRowIndexToView(0);

       
       
        jTable1.repaint();
        
        l = 0;
    }//GEN-LAST:event_jButton1ActionPerformed

    private void jComboBox1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jComboBox1ActionPerformed
    }//GEN-LAST:event_jComboBox1ActionPerformed

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        try {
            MessageFormat headerFormat = new MessageFormat("Извештај за " + mesecString + "-" + godinaString);
            MessageFormat footerFormat = new MessageFormat("- {0} -");
            jTable1.print(JTable.PrintMode.FIT_WIDTH, headerFormat, footerFormat);

        } catch (PrinterException pe) {
            System.err.println("Error printing: " + pe.getMessage());
        }
    }//GEN-LAST:event_jButton2ActionPerformed

    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new Reports().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private javax.swing.JComboBox jComboBox1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private com.toedter.calendar.JMonthChooser jMonthChooser1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable jTable1;
    private com.toedter.calendar.JYearChooser jYearChooser1;
    // End of variables declaration//GEN-END:variables

    public ArrayList removeDuplicateWithOrder(ArrayList arlList) {
        Set set = new HashSet();
        ArrayList newList = new ArrayList();
        for (Iterator iter = arlList.iterator(); iter.hasNext();) {
            Object element = iter.next();
            if (set.add(element)) {
                newList.add(element);
            }
        }
        arlList.clear();
        arlList.addAll(newList);
        return arlList;
    }

    public ArrayList getList() {
        ArrayList list = new ArrayList();
        try {
            java.sql.Connection connection = null;
            connection = DriverManager.getConnection(url, username, password);
            java.sql.PreparedStatement t = connection.prepareStatement("SELECT * from  porotnik");
            ResultSet trs = null;
            trs = t.executeQuery();

            while (trs.next()) {
                list.add(trs.getString("ime"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Izmena.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }
}
